package com.pai.dao.Department;

import com.mysql.cj.util.StringUtils;
import com.pai.dao.BaseDao;
import com.pai.entity.Department;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class DepartmentDaoImpl implements DepartmentDao{
    /**
     * 获取列表
     * @param connection
     * @return
     * @throws Exception
     */
    @Override
    public List<Department> getList(Connection connection) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Department> resultList = new ArrayList<Department>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from (department left join department farDep on department.parentId = farDep.id) left join personnel on department.principalId = personnel.id ");



        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Department _item = new Department();
            _item.setId(rs.getInt("department.id"));
            _item.setDepNo(rs.getString("department.depNo"));
            _item.setDepName(rs.getString("department.depName"));
            _item.setDepType(rs.getInt("department.depType"));
            _item.setParentId(rs.getInt("farDep.id"));
            _item.setParentName(rs.getString("farDep.depName"));
            _item.setDuty(rs.getString("department.duty"));
            _item.setOtherParameter(rs.getString("department.otherParameter"));
            _item.setPrincipalId(rs.getInt("department.principalId"));
            _item.setPrincipalName(rs.getString("personnel.name"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 根据部门类型获取部门
     * @param connection
     * @param depType 0：教学部门（二级学院） 1：行政部门（学工处） 2：班级
     * @return
     * @throws Exception
     */
    @Override
    public List<Department> getList(Connection connection, String depType) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Department> resultList = new ArrayList<Department>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from (department left join department farDep on department.parentId = farDep.id) left join personnel on department.principalId = personnel.id where department.depType = ? ");
        params.add(depType);



        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Department _item = new Department();
            _item.setId(rs.getInt("department.id"));
            _item.setDepNo(rs.getString("department.depNo"));
            _item.setDepName(rs.getString("department.depName"));
            _item.setDepType(rs.getInt("department.depType"));
            _item.setParentId(rs.getInt("farDep.id"));
            _item.setParentName(rs.getString("farDep.depName"));
            _item.setDuty(rs.getString("department.duty"));
            _item.setOtherParameter(rs.getString("department.otherParameter"));
            _item.setPrincipalId(rs.getInt("department.principalId"));
            _item.setPrincipalName(rs.getString("personnel.name"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取班级部门列表
     * @param connection
     * @param depId 父级部门id
     * @return
     * @throws Exception
     */
    @Override
    public List<Department> getDepClassList(Connection connection, String depId) throws Exception {

        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Department> resultList = new ArrayList<Department>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from (department left join department farDep on department.parentId = farDep.id) left join personnel on department.principalId = personnel.id where department.parentId = ? ");
        params.add(depId);




        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Department _item = new Department();
            _item.setId(rs.getInt("department.id"));
            _item.setDepNo(rs.getString("department.depNo"));
            _item.setDepName(rs.getString("department.depName"));
            _item.setDepType(rs.getInt("department.depType"));
            _item.setParentId(rs.getInt("farDep.id"));
            _item.setParentName(rs.getString("farDep.depName"));
            _item.setDuty(rs.getString("department.duty"));
            _item.setOtherParameter(rs.getString("department.otherParameter"));
            _item.setPrincipalId(rs.getInt("department.principalId"));
            _item.setPrincipalName(rs.getString("personnel.name"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 根据负责人id获取部门信息
     * @param connection
     * @param principalId  负责人Id
     * @return
     * @throws Exception
     */
    @Override
    public List<Department> getListByPrincipalId(Connection connection, String principalId) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        List<Department> resultList = new ArrayList<Department>();

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from (department left join department farDep on department.parentId = farDep.id) left join personnel on department.principalId = personnel.id where department.principalId = ? ");
        params.add(principalId);



        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Department _item = new Department();
            _item.setId(rs.getInt("department.id"));
            _item.setDepNo(rs.getString("department.depNo"));
            _item.setDepName(rs.getString("department.depName"));
            _item.setDepType(rs.getInt("department.depType"));
            _item.setParentId(rs.getInt("farDep.id"));
            _item.setParentName(rs.getString("farDep.depName"));
            _item.setDuty(rs.getString("department.duty"));
            _item.setOtherParameter(rs.getString("department.otherParameter"));
            _item.setPrincipalId(rs.getInt("department.principalId"));
            _item.setPrincipalName(rs.getString("personnel.name"));
            resultList.add(_item);
        }
        BaseDao.closeResource(null,preparedStatement,rs);
        return resultList;
    }

    /**
     * 获取项详情
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public Department getItemDetail(Connection connection, String id) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        Department item = null;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from (department left join department farDep on department.parentId = farDep.id) left join personnel on department.principalId = personnel.id where department.id = ? ");
        params.add(id);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Department _item = new Department();
            _item.setId(rs.getInt("department.id"));
            _item.setDepNo(rs.getString("department.depNo"));
            _item.setDepName(rs.getString("department.depName"));
            _item.setDepType(rs.getInt("department.depType"));
            _item.setParentId(rs.getInt("farDep.id"));
            _item.setParentName(rs.getString("farDep.depName"));
            _item.setDuty(rs.getString("department.duty"));
            _item.setOtherParameter(rs.getString("department.otherParameter"));
            _item.setPrincipalId(rs.getInt("department.principalId"));
            _item.setPrincipalName(rs.getString("personnel.name"));
            item = _item;
        }
        BaseDao.closeResource(null, preparedStatement, rs);
        return item;
    }

    /**
     * 获取项
     * @param connection
     * @param depName  部门名称
     * @param depType  部门类型
     * @return
     * @throws Exception
     */
    @Override
    public Department getItemDetail(Connection connection, String depName, String depType) throws Exception {
        PreparedStatement preparedStatement = null;
        ResultSet rs = null;
        Department item = null;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();
        //没有查询条件限制下的原版查询语句
        sql.append("select * from department");
        if(!StringUtils.isNullOrEmpty(depName)){
            sql.append(" where depName = ? ");
            params.add(depName);
        }
        if(!StringUtils.isNullOrEmpty(depType)){
            sql.append(" and depType = ? ");
            params.add(depType);
        }


        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, rs, sql.toString(), params.toArray());

        while (rs.next()){
            Department _item = new Department();
            _item.setId(rs.getInt("id"));
            _item.setDepNo(rs.getString("depNo"));
            _item.setDepName(rs.getString("depName"));
            _item.setDepType(rs.getInt("depType"));
            item = _item;
        }
        BaseDao.closeResource(null, preparedStatement, rs);
        return item;
    }

    /**
     * 新增
     * @param connection
     * @param item
     * @return
     * @throws Exception
     */
    @Override
    public int add(Connection connection, Department item) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("insert into department (depNo, depName, depType, parentId, duty, otherParameter, principalId) values (?, ?, ?, ?, ?, ?, ?)");
        params.add(item.getDepNo());
        params.add(item.getDepName());
        params.add(item.getDepType());
        if(item.getParentId() == 0){
            params.add(null);
        }
        else {
            params.add(item.getParentId());
        }
        params.add(item.getDuty());
        params.add(item.getOtherParameter());
        if(item.getPrincipalId() != 0) params.add(item.getPrincipalId());
        else params.add(null);

        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }



    /**
     * 更新
     * @param connection
     * @param item
     * @return
     * @throws Exception
     */
    @Override
    public int update(Connection connection, Department item) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("update department set depNo = ?, depName = ?, depType = ?, parentId = ?, duty = ?, otherParameter = ?, principalId = ? where id = ?");
        params.add(item.getDepNo());
        params.add(item.getDepName());
        params.add(item.getDepType());
        if(item.getParentId() != 0) params.add(item.getParentId());
        else params.add(null);
        params.add(item.getDuty());
        params.add(item.getOtherParameter());
        if(item.getPrincipalId() != 0) params.add(item.getPrincipalId());
        else params.add(null);
        params.add(item.getId());



        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }

    /**
     * 删除
     * @param connection
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public int delete(Connection connection, String id) throws Exception {
        PreparedStatement preparedStatement = null;
        int rs = 0;

        StringBuffer sql = new StringBuffer();
        List<Object> params = new ArrayList<Object>();

        sql.append("delete from department where id = ?");
        params.add(id);


        System.out.println(sql);
        rs = BaseDao.execute(connection, preparedStatement, sql.toString(), params.toArray());

        BaseDao.closeResource(null, preparedStatement, null);
        return rs;
    }
}
